Dataverse Virtual Tables CRUD operations

Dataverse Virtual Tables CRUD operations:

Introduction

You may already be familiar with Virtual Entities or are they are now called Virtual Tables. As detailed in this article from Stéphan Drouard, Virtual Tables are a custom table registered in your Dataverse environment but that is not stored in your Dataverse environment.

A virtual table is a custom table that has fields containing data from an external data source. For the user, the virtual entity is displayed in your app like a regular entity but contains data that is sourced from an external database.

In the first version of the Virtual Tables feature, the data provided only read-only access. But this has changed and now all CRUD operations are available!

Set up

For this article, we will start by using exactly the same provider as in Drouard's article: an OData V4 Read-Write endpoint powered by the OData organization via https://services.odata.org/. Be sure to select a Write-Read endpoint. Checking it is easy: you must have a token in the URL of the service.

Also, ensure that the service has an OData V4 web service one! For example, if the URL from https://services.odata.org/ contains a V3, the data is not pulled into Dataverse!

Then, as we've been used to work with Virtual Tables, we just need to create a new custom table, linked it to the OData V4 Data Source created, and link the fields to the Advertisement endpoint.

And that's it! In your Dataverse environment, the table Advertisement will display data directly from the OData V4 endpoint.

• From Dataverse
  • 
• From OData endpoint
  • 

CRUD operations

The data is pulled from the web service and displayed directly in the form.

Fields that aren't read-only can be updated. Without needing any more configurations, Dataverse will make an HTTP PATCH request and update the record.

Now, we can also create new records for Virtual Tables and the OOB OData V4 feature will create them into the external database! And what's a Create if there is no Delete? Because yes, the Delete operation works also OOB!

 As we will notice in the GIF below, at the time of this article, there was a bug with all fields entered in the form not being saved at creation. But an Update did the trick.
 

Extend with code

Virtual Tables are just like regular tables, except they aren't stored inside Dataverse. Can we deal with them via the Web API or via the SDK? Yes we can!

As for a model-driven, CRUD operations are also available from the Dataverse Web API. And why stop when it's good? SDK operations can also deal with virtual tables records!

It's even possible to create Data Provider and Data Source directly via the SDK. For more information, refer to Microsoft's documentation.

And guess what: plugins can also be register upon virtual tables records!

Custom Endpoint

If you don't have an OData V4 endpoint nor an Azure Cosmo DB, but you still want to use Virtual Tables, then you need to create your own Data Provider. And CRUD operations are also very well supported for custom data providers.

In short, plugins can be registered for the following events:

• Create
• Retrieve
• RetrieveMultiple
• Update
• Delete

Once these plugins are registered, linked to a new Data provider, and then have a Virtual Table linked to them, users will have the feeling to work with Dataverse tables! See Microsoft's documentation for more information about registering those plugins.

Security

Regarding the security model applied to those tables, they only have two modes: either on or off. Indeed, only organization-owned tables are supported. Also, field-level security is not supported for those kinds of tables.

Author's view

Virtual Tables are a very good tool to have in your Microsoft Daverse set of skills. The OData v4 integration is very impressive: in less than 5 minutes, new tables can be created in a Dataverse environment and users can really work with the data.

Support for all the CRUD operations opens many usage possibilities for this feature. Nevertheless, the drawbacks are still the same:

• All tables in the external data source must have an associated GUID primary key. An integer or string ID is not enough. This is the major drawback for the feature.
• Charts and dashboards are not supported for virtual tables. I guess that's where Power BI comes handy :)

References

• Microsoft's documentation
• Microsoft's developer documentation

Dataverse Virtual Tables CRUD operations